﻿/*****************************************
 * 
 *  copyright (c) by ylshen
 * 
 *  author : ylshen (syl.lucky@gmail.com)
 * 
 **************************************** */

using System;
using System.Data;
using System.Data.SqlClient;
using System.Collections.Generic;
using System.Text;
using JYStockAnalyser.Model;

namespace JYStockAnalyser.DA
{
    /// <summary>
    /// 指标类型、指标的数据映射器
    /// </summary>
    public class GuideLineMapper : ORMapper
    {

        public List<GuideLine> FindGuideLines(string stockNO, int beginYear, int endYear, GuideLineType type)
        {
            string filter = String.Format(" WHERE stock = '{0}' AND year >= {1} AND year <= {2}", stockNO, beginYear, endYear);
            return m_findGuideLinesByFilter(filter, type);
        }

        public List<GuideLine> FindGuideLines(string stockNO, string analyseTimeType, YearQuarter beginQuarter, YearQuarter endQuarter, GuideLineType type)
        {
            string filter = null;
            if (analyseTimeType.Equals("季度"))
            {
                filter = String.Format(" WHERE stock = '{0}' AND ((year > {1} AND year < {2}) OR (year = {1} AND quarter >= {3}) OR (year = {2} AND quarter <= {4}))", stockNO, beginQuarter.Year, endQuarter.Year, beginQuarter.Quarter, endQuarter.Quarter);
            }
            else if (analyseTimeType.Equals("年"))
            {
                filter = String.Format(" WHERE stock = '{0}' AND year >= {1} AND year <= {2}", stockNO, beginQuarter.Year, endQuarter.Year);
            }
            return m_findGuideLinesByFilter(filter, type);
        }

        public List<GuideLine> FindGuideLines(string stockNO, YearQuarter quarter, GuideLineType[] types)
        {
            List<GuideLine> lines = new List<GuideLine>();

            StringBuilder filter = new StringBuilder();
            filter.AppendFormat(" WHERE stock = '{0}' AND year = {1} AND quarter = {2}", stockNO, quarter.Year, quarter.Quarter);
            foreach (GuideLineType type in types)
            {
                GuideLine line = m_findGuideLineByFilter(filter.ToString(), type);
                if (line == null)
                {
                    line = GuideLine.CreateUnDefinedValueGuideLine(type, quarter);
                }
                lines.Add(line);
            }
            return lines;
        }

        public List<GuideLine> FindGuideLines(string stock, YearQuarter quarter, string report)
        {
            List<GuideLineType> types = FindTypes(report, 2);
            return FindGuideLines(stock, quarter, types.ToArray());
        }

        /// <summary>
        /// 获取一个报表下的所有指标
        /// </summary>
        /// <param name="report">报表名称</param>
        /// <param name="stockType">股标类型 1 金融类　2 非金融类</param>
        /// <returns></returns>
        public List<GuideLineType> FindTypes(string report, int stockType)
        {
            int reportType = 0;
            if (report.Equals("资产负债表")) { reportType = 1; }
            else if (report.Equals("利润分配表")) { reportType = 2; }
            else if (report.Equals("现金流量表")) { reportType = 3; }
            else if (report.Equals("成长性")) { reportType = 4; }
            else if (report.Equals("经营能力")) { reportType = 5; }
            else if (report.Equals("盈利能力")) { reportType = 6; }
            else if (report.Equals("偿债能力")) { reportType = 7; }
            else if (report.Equals("现金流量")) { reportType = 8; }
            else if (report.Equals("财务速览")) { reportType = 9; }
            else if (report.Equals("主要财务指标")) { reportType = 10; }
            else if (report.Equals("投资与收益zh")) { reportType = 16; }
            else if (report.Equals("盈利能力zh")) { reportType = 12; }
            else if (report.Equals("经营能力zh")) { reportType = 13; }
            else if (report.Equals("资本构成zh")) { reportType = 14; }
            else if (report.Equals("偿债能力zh")) { reportType = 15; }
            else if (report.Equals("资产负债表(新)")) { reportType = 91; }
            else if (report.Equals("利润分配表(新)")) { reportType = 92; }
            else if (report.Equals("现金流量表(新)")) { reportType = 93; }


            string filter = null;
            string order = null;
            if (stockType == 1)
            {
                filter = String.Format(" WHERE report = {0} AND (type = 3 OR type = 2) ", reportType);
                order = "order2";
            }
            else if (stockType == 2)
            {
                filter = String.Format(" WHERE report = {0} AND (type = 1 OR type = 2) ", reportType);
                order = "order1";
            }

            return m_findGuideLineTypesByFilter(filter, order);
        }

        public decimal[] GetEstimateValue(string stockNO, YearQuarter quarter)
        {
            string SQL = String.Format("SELECT v1, v2, v3, v4, v5 FROM t_guidelines_20001 WHERE stock = '{0}' AND [year] = {1} AND quarter = {2}", stockNO, quarter.Year, quarter.Quarter);
            using (IDataReader reader = Excute_Reader(SQL))
            {
                if (reader.Read())
                {
                    return new decimal[] { reader.GetDecimal(0), reader.GetDecimal(1), reader.GetDecimal(2), reader.GetDecimal(3), reader.GetDecimal(4) };
                }
                else
                {
                    return null;
                }
            }
        }

        public void InsertORUpdateEstimateValues(string stockNO, YearQuarter quarter, decimal v1, decimal v2, decimal v3, decimal v4, decimal v5)
        {
            SqlConnection cn = CreateConnection();
            string sql = "IF EXISTS (SELECT * FROM t_guideLines_20001 WHERE stock = @stock AND year = @year AND quarter = @quarter)" +
                " BEGIN " +
                " UPDATE t_guideLines_20001 SET v1 = @v1, v2=@v2, v3=@v3, v4=@v4, v5=@v5 WHERE stock = @stock AND year = @year AND quarter = @quarter" +
                " END " +
                " ELSE BEGIN " +
                "INSERT t_guideLines_20001 (stock, [year], quarter,v1, v2, v3, v4, v5) VALUES(@stock, @year, @quarter, @v1, @v2, @v3, @v4, @v5)" +
                " END ";
            SqlCommand cmd = new SqlCommand(sql, cn);
            cmd.Parameters.Add(new SqlParameter("@stock", stockNO));
            cmd.Parameters.Add(new SqlParameter("@year", quarter.Year));
            cmd.Parameters.Add(new SqlParameter("@quarter", quarter.Quarter));
            cmd.Parameters.Add(new SqlParameter("@v1", v1));
            cmd.Parameters.Add(new SqlParameter("@v2", v2));
            cmd.Parameters.Add(new SqlParameter("@v3", v3));
            cmd.Parameters.Add(new SqlParameter("@v4", v4));
            cmd.Parameters.Add(new SqlParameter("@v5", v5));

            cn.Open();
            try
            {
                cmd.ExecuteNonQuery();
            }
            catch (System.Exception err)
            {
                Loger.WriteErrorLog(err.ToString());
                throw new ORException(ORException.ERROR_OTHER);
            }
            finally
            {
                cn.Close();
            }
        }

        public void InsertORUpdateGuideLine(string stockNO, GuideLine line)
        {
            SqlConnection cn = CreateConnection();
            string sql = "IF EXISTS (SELECT * FROM t_guideLines_" + line.Type.NO.ToString() + " WHERE stock = @stock AND year = @year AND quarter = @quarter)" +
                " BEGIN " +
                " UPDATE t_guideLines_" + line.Type.NO.ToString() + " SET [value] = @value WHERE stock = @stock AND year = @year AND quarter = @quarter" +
                " END " +
                " ELSE BEGIN " +
                "INSERT t_guideLines_" + line.Type.NO.ToString() + " (stock, [year], quarter,[value]) VALUES(@stock, @year, @quarter, @value)" +
                " END ";

            SqlCommand cmd = new SqlCommand(sql, cn);
            cmd.Parameters.Add(new SqlParameter("@stock", stockNO));
            cmd.Parameters.Add(new SqlParameter("@year", line.YearQuarter.Year));
            cmd.Parameters.Add(new SqlParameter("@quarter", line.YearQuarter.Quarter));
            cmd.Parameters.Add(new SqlParameter("@value", line.Value));

            cn.Open();
            try
            {
                cmd.ExecuteNonQuery();
            }
            catch (System.Exception err)
            {
                Loger.WriteErrorLog(err.ToString());
                throw new ORException(ORException.ERROR_OTHER);
            }
            finally
            {
                cn.Close();
            }
        }

        #region private methods

        private GuideLine m_findGuideLineByFilter(string filter, GuideLineType type)
        {
            List<GuideLine> lines = m_findGuideLinesByFilter(filter, type);
            return lines.Count == 0 ? null : lines[0];
        }

        private List<GuideLine> m_findGuideLinesByFilter(string filter, GuideLineType type)
        {
            string sql = "SELECT [year], quarter, [value] FROM t_guidelines_" + type.NO.ToString() + filter + " ORDER BY year, quarter";
            List<GuideLine> lines = new List<GuideLine>();
            using (SqlDataReader reader = Excute_Reader(sql))
            {
                while (reader.Read())
                {
                    GuideLine line = new GuideLine();
                    line.Type = type;
                    line.YearQuarter.Year = reader.GetInt32(0);
                    line.YearQuarter.Quarter = reader.GetInt32(1);
                    line.Value = reader.GetDecimal(2);
                    lines.Add(line);
                }
            }
            return lines;
        }

        private GuideLineType m_findGuideLineTypeByFilter(string filter)
        {
            List<GuideLineType> types = m_findGuideLineTypesByFilter(filter, "order1");
            return types.Count == 0 ? null : types[0];
        }

        private List<GuideLineType> m_findGuideLineTypesByFilter(string filter, string order)
        {
            string sql = String.Format("SELECT name, [no], isImportant, unit FROM t_GLs{0} ORDER BY {1}", filter, order);
            List<GuideLineType> types = new List<GuideLineType>();
            using (IDataReader reader = Excute_Reader(sql))
            {
                while (reader.Read())
                {
                    GuideLineType type = new GuideLineType();
                    type.Name = GetReaderString(reader, 0);
                    type.NO = GetReaderInt32Value(reader, 1);
                    type.IsImportant = GetReaderBooleanValue(reader, 2);
                    type.Unit = GetReaderString(reader, 3);
                    types.Add(type);
                }
            }
            return types;
        }

        #endregion
    }
}
